###############################################################################
# FL cleaning
#
# 10 August 2022
# sbaltz
###############################################################################

import pandas as pd

RAW_FILE_NAME = "../transcribed/columbia_scraped.csv"
SAVE_FILE_NAME = "../ready/columbia_cleaned.csv"
COUNTY_NAME = "COLUMBIA"

ct = pd.read_csv(RAW_FILE_NAME)

cleaned = pd.DataFrame()

office = ""
for i in range(len(ct)):
    if "Vote for" in str(ct["candidate"][i]):
        office = str(ct["candidate"][i])
    if str(ct["original"][i]) != "nan" and str(ct["audited"][i]) != "nan":
        try:
            state = "FLORIDA"
            county = COUNTY_NAME
            candidate = ct["candidate"][i]
            original = int(ct["original"][i])
            audited = int(ct["audited"][i])
            difference = audited - original
            method = "AUTOMATIC"
            cleaned = pd.concat([cleaned,
                            pd.DataFrame([[state,county,office,\
                            candidate,original,audited,difference,method]])],\
                            ignore_index = True)
        except:
            pass

cleaned.columns = ["state","county","office","candidate","original","audited",\
                   "difference","type"]

cleaned.to_csv(SAVE_FILE_NAME, index=False)
